"""sql 查询文件"""

import logging
import datetime
import time
from global_file import SELJIANLI

from oa.take_data import alls, all_user_id_list

from database.connection import Link_database
from global_file import ZYY_SEL

logger = logging.getLogger(__name__)


class sel_operating(object):
    """自运营查询"""
    def __init__(self, page, name, store_name, start_time, end_time):
        """运营数据查询"""
        self.page = page
        self.name = name
        self.store_name = store_name
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def permissions(self):
        """员工对应的店铺"""
        if self.name in ZYY_SEL:
            sql = """select shop_name from self_storemanage"""
        else:
            sql = """select shop_name from self_storemanage where chief_inspector='{}' or director='{}' or promoters='{}'""".format(self.name, self.name, self.name)

        self.obj.execute(sql)
        q = self.obj.fetchall()
        shop_list = ['']
        for i in q:
            shop_list.append(i[0])

        shop_tuple = tuple(shop_list)

        return shop_tuple

    def sel_name(self):
        """名下所有店铺查询"""
        shop_tuple = self.permissions()

        sql = """select id,statdate,store_name,visitors,through_visitors,drill_visitors,sum_turnover,brush_single_number,brush_single_money,pay_buyers_number,free_visitors,price,conversion_rate,real_pay_buyers_number,real_turnover,members_number,members_rate,refund_money,real_money from operating where store_name in {} and statdate between {} and {} limit {},20""".format(shop_tuple, self.start_time, self.end_time, self.page)
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def sel_store(self):
        """指定店铺查询"""
        shop_tuple = self.permissions()

        if self.store_name in shop_tuple:
            sql = """select id,statdate,store_name,visitors,through_visitors,drill_visitors,sum_turnover,brush_single_number,brush_single_money,pay_buyers_number,free_visitors,price,conversion_rate,real_pay_buyers_number,real_turnover,members_number,members_rate,refund_money,real_money from operating where store_name='{}' and statdate between {} and {} limit {},20""".format(self.store_name, self.start_time, self.end_time, self.page)
            self.obj.execute(sql)
            q = self.obj.fetchall()

            return q, True
        else:
            return (), False

    def __del__(self):
        self.obj.close()
        self.con.close()


def generaoperating(name):
    """查询自运营所有店铺"""
    con, obj = Link_database()
    if name == 'admin' or name == '蒋鹏':
        sql = """select shop_name,chief_inspector,director,promoters from self_storemanage"""
    else:
        sql = """select shop_name,chief_inspector,director,promoters from self_storemanage where chief_inspector='{}' or director='{}' or promoters='{}'""".format(name, name, name)
    obj.execute(sql)
    q = obj.fetchall()

    data = []
    for i in q:
        a = {
            "shop_name": i[0],
            "chief_inspector": i[1],
            "director": i[2],
            "promoters": i[3]
        }
        data.append(a)

    obj.close()
    con.close()

    return data

def sel_permission(user_id):
    """  检查录入店铺权限"""
    con, obj = Link_database()
    sql = """select id from depar_user where boss = %d or manager = %d or director = %d or tube = %d """ % (user_id,user_id,user_id,user_id)
    obj.execute(sql)
    q = obj.fetchone()
    obj.close()
    con.close()
    return q


def sel_generaoperattube(page,user_name):
    """代运营项目管理"""
    con, obj = Link_database()

    if user_name == 'admin':
        sql = """select id,store_name, project_type, chief_inspecto,director,operator,start_time,end_time,account_entry,amount_account,renewal_time,customer_info,subscription_info,contract_status,store_status,email from genera_operat_tube limit %d,20 """ %(page)
    else:
        sql = """select id,store_name, project_type, chief_inspecto,director,operator,start_time,end_time,account_entry,amount_account,renewal_time,customer_info,subscription_info,contract_status,store_status,email from genera_operat_tube where boss = '%s' or chief_inspecto = '%s' or director = '%s' or operator = '%s' limit %d,20""" % (user_name,user_name,user_name,user_name,page)

    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def sel_comecollection(page):
    """代运营提点回款"""
    con, obj = Link_database()

    sql = """select id,project_type,clearing_time,store_name,sales,brush_single_money,refund_money,real_sales,investment,some,commission,is_settlement,special_note,operator from some_collection limit %d,20""" % page

    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def sel_some_situation(project_type, store_name, page):
    """代运营管理与提点关联查询"""
    con, obj = Link_database()

    sql = """select id,project_type,clearing_time,store_name,sales,brush_single_money,refund_money,real_sales,investment,some,commission,is_settlement,special_note from some_collection where project_type='%s' and store_name='%s' limit %d,20""" % (project_type, store_name, page)

    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


class yunying_log(object):
    """代运营日志查询"""
    def __init__(self, user_id, start_time, end_time, page):
        self.user_id = user_id
        self.page = page
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def deal_with(self,):
        """数据处理"""
        if self.user_id in SELJIANLI:
            self.u_tuple = tuple(alls())
        else:
            u_list = all_user_id_list(self.user_id)
            u_list.append(self.user_id)
            self.u_tuple = tuple(u_list)

    def deal_time(self):
        if not self.start_time and not self.end_time:
            self.start_time = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d'))) - 86400
            self.end_time = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')))

    def perform(self):
        self.deal_with()
        self.deal_time()

        sql = """select a.id,a.statdate,a.shop_name,b.name from job_log as a inner join user as b on a.user_id=b.id where a.user_id in {} and a.statdate 
                      between {} and {} order by a.statdate limit {},20""".format(self.u_tuple, self.start_time, self.end_time, self.page)
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def deal_data(self):
        q = self.perform()
        data = []
        for i in q:
            a = {
                "id": i[0], "statdate": time.strftime('%Y-%m-%d', time.localtime(i[1])), "shop_name": i[2], "name": i[3]
            }
            data.append(a)
        return data

    def __del__(self):
        self.obj.close()
        self.con.close()


class Details_log(object):
    """代运营日志详情查询"""
    def __init__(self, id, shop_name, start_time, end_time):
        self.id = id
        self.shop_name = shop_name
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def sel_data(self):
        if self.id:
            sql = """select a.id,a.statdate,a.shop_name,a.sales,a.brush_money,a.visitors,a.conversion_rate,a.advertising,a.input_rate,a.
                              advertising_rate,a.old_customer_rate,a.purchased,a.collection_rate,a.uv_value,a.ztc_sales,a.
                              ztc_consumption,a.ztc_roi,a.ztc_purchased,a.ztc_collection,a.ztc_purchase_cost,a.ztc_click_cost,a.
                              ztc_uv_value,a.zz_sales,a.zz_consumption,a.zz_roi,a.zz_purchased,a.zz_collection,a.zz_purchase_cost,a.
                              zz_click_cost,a.zz_uv_value,a.content,b.name from job_log as a inner join user as b on a.user_id=b.id where a.id=%d""" % self.id
        else:
            sql = """select a.id,a.statdate,a.shop_name,a.sales,a.brush_money,a.visitors,a.conversion_rate,a.advertising,a.input_rate,a.
                                          advertising_rate,a.old_customer_rate,a.purchased,a.collection_rate,a.uv_value,a.ztc_sales,a.
                                          ztc_consumption,a.ztc_roi,a.ztc_purchased,a.ztc_collection,a.ztc_purchase_cost,a.ztc_click_cost,a.
                                          ztc_uv_value,a.zz_sales,a.zz_consumption,a.zz_roi,a.zz_purchased,a.zz_collection,a.zz_purchase_cost,a.
                                          zz_click_cost,a.zz_uv_value,a.content,b.name from job_log as a inner join user as b on a.user_id=b.id where a.shop_name='%s' and a.statdate between %d and %d""" % (self.shop_name, self.start_time, self.end_time)

        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def deal_with(self):
        q = self.sel_data()
        data = []
        for i in q:
            a = {
                "id": i[0], "statdate": time.strftime('%Y-%m-%d', time.localtime(i[1])), "shop_name": i[2], "sales": i[3], "brush_money": i[4], "visitors": i[5], "conversion_rate": i[6], "advertising": i[7], "input_rate": i[8],
                "advertising_rate": i[9], "old_customer_rate": i[10], "purchased": i[11], "collection_rate": i[12], "uv_value": i[13], "ztc_sales": i[14], "ztc_consumption": i[15], "ztc_roi": i[16], "ztc_purchased": i[17],
                "ztc_collection": i[18], "ztc_purchase_cost": i[19], "ztc_click_cost": i[20], "ztc_uv_value": i[21], "zz_sales": i[22], "zz_consumption": i[23], "zz_roi": i[24], "zz_purchased": i[25], "zz_collection": i[26],
                "zz_purchase_cost": i[27], "zz_click_cost": i[28], "zz_uv_value": i[29], "content": i[30], "name": i[31]
            }
            data.append(a)

        return data

    def __del__(self):
        self.obj.close()
        self.con.close()


def sel_all_shop(user_name):
    """代运营项目管理"""
    con, obj = Link_database()

    sql = """select store_name from genera_operat_tube where boss='%s' or chief_inspecto='%s' or director='%s' or operator='%s'""" % (user_name, user_name, user_name, user_name)

    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


if __name__ == '__main__':
    a = sel_permission(56)
    print(a)